package com.scalar.chiptrack.operations.process.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import org.apache.struts.util.LabelValueBean;

import com.scalar.ScalarException;
import com.scalar.chiptrack.commons.Address;
import com.scalar.chiptrack.commons.Location;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.release.ContactPerson;
import com.scalar.chiptrack.operations.vendor.Vendor;
import com.scalar.chiptrack.utils.StringUtils;

public class NewVendorLocationDAO
{
    private NewVendorLocationDAO() { }

    /**
    * This method used to load Vendor Name and Vendor-Id into List for particular processtype
    * @param dbTransaction
    * @param processType
    * @return vendorsList
    * @throws com.scalar.ScalarException
    */
    public static ArrayList<LabelValueBean> loadVendorsByPSType( DBTransaction dbTransaction, String processType ) throws ScalarException
    {
        ArrayList<LabelValueBean> vendorsList 	= new ArrayList<LabelValueBean>();
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        ResultSet rs 			= null;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt 	= conn.prepareStatement( "SELECT DISTINCT V.V_NAME, V.VENDOR_ID, L.LOCATION_ID FROM LOCATION L, VENDOR V WHERE L.VENDOR_ID = V.VENDOR_ID AND L.L_TYPE = ? AND L_IS_DELETED = 'N' ORDER BY V.V_NAME" );
            pstmt.setString( 1, processType );
            rs	= pstmt.executeQuery();

            while ( rs.next() )
            {
                vendorsList.add( new LabelValueBean ( rs.getString ( "V_NAME" ), rs.getString( "VENDOR_ID" ) ) );
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ("Could not get the Vendors from the database.");
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException ignore) {}
            }

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }

            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return vendorsList;
    }

    public static Vendor loadVendorLocationsDetails( DBTransaction dbTransaction, int vendorId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;

        Vendor vendor           = null;
        Location location       = null;
        ArrayList<Location> locationsList = null;
        Address address         = null;
        int firstRecord         = 0;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT V.Vendor_ID, V_Number, V_Name, V_Net_Terms, V_PO_Frequency, V_Pay_By_Date, V_Comments, V_Sales_Contact, V_Is_Deleted, V_Shipper_ID, L.Location_ID, L.Address_ID, L.L_Contact_Person, L.L_Type, L.L_Vendor_Name, L.L_Is_Deleted, Address_1, Address_2, City, State, Zipcode, Country, Phone_Number, Fax_Number, Email, Cell_Number, Address_3, Address_4, Contact_Person FROM VENDOR V, LOCATION L, ADDRESS A WHERE V.VENDOR_ID = L.VENDOR_ID AND L.ADDRESS_ID = A.ADDRESS_ID AND V.VENDOR_ID = ?" );

            pstmt.setInt( 1, vendorId );

            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                if( firstRecord == 0 )
                {
                    vendor          = new Vendor();
                    locationsList   = new ArrayList<Location>();

                    vendor.setVendorId      ( rs.getInt("Vendor_ID") );
                    vendor.setVendorNumber  ( rs.getString("V_Number") );
                    vendor.setVendorName    ( rs.getString("V_Name") );
                    vendor.setNetTerms      ( rs.getInt("V_Net_Terms") );
                    vendor.setPoFrequency   ( rs.getString("V_PO_Frequency") );
                    vendor.setPayByDate     ( rs.getInt("V_Pay_By_Date") );
                    vendor.setComments      ( rs.getString("V_Comments") );
                    vendor.setSalesContact  ( rs.getString("V_Sales_Contact") );
                    vendor.setIsDeleted     ( ( rs.getString("V_Is_Deleted") == "Y" ) ? true : false );
                    vendor.setShipperId     ( rs.getInt("V_SHIPPER_ID") );

                    vendor.setLocationsList( locationsList );
                }

                location    = new Location();
                address     = new Address();

                location.setLocationId  ( rs.getInt("Location_ID") );
                location.setAddressId   ( rs.getInt("Address_ID") );
                location.setVendorId    ( vendor.getVendorId() );
                location.setContactPerson( rs.getString("L_Contact_Person") );
                location.setLocationType( rs.getString("L_Type") );
                location.setVendorName  ( rs.getString("L_Vendor_Name") );
                location.setIsCancel    ( ( rs.getString("L_Is_Deleted") == "Y" ) ? true : false );

                address.setAddressId    ( location.getAddressId() );
                address.setAddress1     ( rs.getString("Address_1") );
                address.setAddress2     ( rs.getString("Address_2") );
                address.setCity         ( rs.getString("City") );
                address.setState        ( rs.getString("State") );
                address.setZipCode      ( rs.getString("Zipcode") );
                address.setCountry      ( rs.getString("Country") );
                address.setPhoneNumber  ( rs.getString("Phone_Number") );
                address.setFaxNumber    ( rs.getString("Fax_Number") );
                address.setEmail        ( rs.getString("Email") );
				address.setCellNumber   ( rs.getString("Cell_Number") );
				address.setAddress3	    ( rs.getString("Address_3") );
				address.setAddress4     ( rs.getString("Address_4") );
				address.setContactPerson( rs.getString("Contact_Person") );

                location.setLocationAddress( address );
                locationsList.add( location );

                firstRecord++;
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Vendor Details from database", sqlex);
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) { }
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) { }
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) { }
            }
        }

        return vendor;
   }

    public static ArrayList<ContactPerson> loadVendorContactPersonsList( DBTransaction dbTransaction, int vendorId, String processType, int releaseInfoSetupId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;

        ArrayList<ContactPerson> contactPersonsList    = new ArrayList<ContactPerson>();
        ContactPerson cp                = null;
        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( "SELECT VENDOR_ID, VENDOR_NAME, PERSON_NAME, PERSON_EMAIL, IS_SELECTED FROM FN_LOAD_VENDORS_CONTACT_PERSONS_DETAILS( ?, ?, ? )" );

            pstmt.setInt    ( 1, releaseInfoSetupId );
            pstmt.setInt    ( 2, vendorId );
            pstmt.setString ( 3, processType );

            rs = pstmt.executeQuery();
            while( rs.next() )
            {
                cp = new ContactPerson();
                cp.setVendorId  ( rs.getInt( "VENDOR_ID" ) );
                cp.setVendorName( rs.getString( "VENDOR_NAME" ) );
                cp.setName      ( rs.getString( "PERSON_NAME" ) );
                cp.setEmail     ( rs.getString( "PERSON_EMAIL" ) );
                cp.setSelected  ( ( rs.getInt( "IS_SELECTED" ) == 0 ) ? true : false );
                contactPersonsList.add( cp );
            }
        }
        catch( Exception e)
        {
            throw new ScalarException ("Could not get the Vendor Contact persons list from the database.");
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException ignore) {}
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return contactPersonsList;
    }

    public static String loadVendorContactPersonsEmails( DBTransaction dbTransaction, int vendorId, String processType ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        ArrayList<String> emailsList    = new ArrayList<String>();
        String[] emails         = null;
        String email            = null;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT VENDOR_ID, VENDOR_NAME, PERSON_NAME, PERSON_EMAIL FROM FN_LOAD_VENDORS_CONTACT_PERSONS_DETAILS( -1, ?, ? )" );

            pstmt.setInt    ( 1, vendorId );
            pstmt.setString ( 2, processType );

            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                emailsList.add ( rs.getString ( "PERSON_EMAIL" ) );
            }
            emails = ( String[] ) emailsList.toArray( new String[0] );
            email = StringUtils.concatStrings( emails, "," );
            email = StringUtils.removeDuplicateEmailIds ( email );

        }
        catch( Exception e)
        {
            throw new ScalarException ("Could not get the Vendor Contact persons Emails from the database.");
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException ignore) {}
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return email;
    }


    public static int getVendorId( DBTransaction dbTransaction, int locationId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        int vendorId            = -1;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT DISTINCT VENDOR_ID From LOCATION WHERE LOCATION_ID = ?" );
            pstmt.setInt(1, locationId);

            rs = pstmt.executeQuery();

            if ( rs.next() )
            {
                vendorId = rs.getInt( "VENDOR_ID" );
            }
        }
        catch( Exception e)
        {
            throw new ScalarException ("Could not get the Vendor ID from the database.");
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException ignore) {}
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return vendorId;
    }
    public static HashMap<String, String> getVendorAddressDetailsMap ( String processType  )
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        HashMap<String, String> addressMap = new HashMap<String, String>();

        try
        {
            conn = DataSource.getConnection();

           pstmt = conn.prepareStatement( "SELECT V.Vendor_ID, V_Number, V_Name, V_Net_Terms, V_PO_Frequency, V_Pay_By_Date, V_Comments, V_Sales_Contact, V_Is_Deleted, V_Shipper_ID, L.Location_ID, L.Address_ID, L.L_Contact_Person, L.L_Type, L.L_Vendor_Name, L.L_Is_Deleted, Address_1, Address_2, City, State, Zipcode, Country, Phone_Number, Fax_Number, Email, Cell_Number, Address_3, Address_4, Contact_Person FROM VENDOR V, LOCATION L, ADDRESS A WHERE V.VENDOR_ID = L.VENDOR_ID AND L.ADDRESS_ID = A.ADDRESS_ID AND L_TYPE = ? " );
            pstmt.setString(1, processType);
            rs = pstmt.executeQuery();
            Address address= new Address();    
            while(rs.next())
            {
                address.setVendorId(rs.getInt("Vendor_ID"));
                address.setVendorName(rs.getString("V_Name"));
                address.setAddress1(rs.getString("Address_1"));
                address.setAddress2(rs.getString("Address_2"));
                address.setCity(rs.getString("City"));
                address.setState(rs.getString("State"));
                address.setZipCode(rs.getString("Zipcode"));
                address.setCountry(rs.getString("Country"));
                address.setPhoneNumber(rs.getString("Phone_Number"));
                address.setFaxNumber(rs.getString("Fax_Number"));
                address.setEmail(rs.getString("Email"));
                address.setCellNumber(rs.getString("Cell_Number"));
                address.setContactPerson(rs.getString("Contact_Person"));
                addressMap.put(address.getVendorId()+"", address.getVendorAddressAsString() );
            }
            success = true;

        }
        catch(Exception e){e.printStackTrace();}
        finally
        {
                try
                {
                    if (success == true)
                    conn.commit();
                    else
                    conn.rollback();
                }
                catch (SQLException ignore) {}

                if ( rs != null )
                {
                    try { rs.close(); } catch ( SQLException ignore ) {}
                }
                if (pstmt != null)
                {
                    try { pstmt.close(); } catch (SQLException ignore){}
                }
                if (conn != null)
                {
                    try { conn.close(); } catch (SQLException ignore){}
                }
        }
        return addressMap;
    }

}
